package com.zjl.hiring.company.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zjl.hiring.common.DBObject;
import com.zjl.hiring.common.util.DBUtil;
import com.zjl.hiring.company.entity.PositionEntity;
import com.zjl.hiring.talent.entity.ResumeEntity;
import com.zjl.hiring.talent.entity.TalentEntity;

public class PositionDao {

	public static List<PositionEntity> listAllPosition() {
		List<PositionEntity> positionList = new ArrayList<>();
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT p.id, c.`name` AS 'company_name', c.address, c.slogan, p.`name` AS 'position_name', p.salary1, p.salary2, p.release_time ");
		sb.append("FROM position p, company c ");
		sb.append("WHERE p.company_id = c.id");
		DBObject dbObject = null;
		ResultSet resultSet = null;
		try {
			dbObject = DBUtil.executeQuery(sb.toString());
			resultSet = dbObject.getResultSet();
			while (resultSet.next()) {
				PositionEntity position = new PositionEntity();
				position.setId(resultSet.getInt("id"));
				position.setCompanyName(resultSet.getString("company_name"));
				position.setCompanyAddress(resultSet.getString("address"));
				position.setCompanySlogan(resultSet.getString("slogan"));
				position.setName(resultSet.getString("position_name"));
				position.setSalary1(resultSet.getInt("salary1"));
				position.setSalary2(resultSet.getInt("salary2"));
				position.setReleaseTime(resultSet.getTimestamp("release_time"));
				positionList.add(position);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(dbObject);
		}
		return positionList;
	}

	public PositionEntity get(int positionId) {
		PositionEntity position = null;
		DBObject dbObject = null;
		ResultSet resultSet = null;
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("p.id, p.name, p.duty, p.ability, p.salary1, p.salary2, p.release_time, ");
		sql.append("c.name AS 'company_name', c.linkman, c.phone, c.email, c.numbers, c.slogan, c.address ");
		sql.append("FROM position p, company c ");
		sql.append("WHERE p.company_id=c.id AND p.id=?");

		try {
			dbObject = DBUtil.executeQuery(sql.toString(), positionId);
			resultSet = dbObject.getResultSet();
			if (resultSet.next()) {
				position = new PositionEntity();
				position.setId(resultSet.getInt("id"));
				position.setName(resultSet.getString("name"));
				position.setDuty(resultSet.getString("duty"));
				position.setAbility(resultSet.getString("ability"));
				position.setSalary1(resultSet.getInt("salary1"));
				position.setSalary2(resultSet.getInt("salary2"));
				position.setReleaseTime(resultSet.getTimestamp("release_time"));
				position.setCompanyName(resultSet.getString("company_name"));
				position.setCompanyLinkman(resultSet.getString("linkman"));
				position.setCompanyPhone(resultSet.getString("phone"));
				position.setCompanyEmail(resultSet.getString("email"));
				position.setCompanyNumbers(resultSet.getString("numbers"));
				position.setCompanySlogan(resultSet.getString("slogan"));
				position.setCompanyAddress(resultSet.getString("address"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			position = null;
			e.printStackTrace();
		} finally {
			DBUtil.close(dbObject);
		}
		return position;
	}

}
